-- =============================================
-- This procedure retrieves all the associated tags for a given component.
-- For nested tags, it retrieves all ancestors leading up to the tag for a
-- more user friendly display. This procedure is currently called by Admin\
-- Admin\TaggedPage\TaggedPageCategories.cfm.
--
-- MODIFICATION HISTORY:
--   DATE        USER          ACTION
--   02/24/2004  C.Jewell      Created.
-- =============================================

Create  PROCEDURE amsp_GetTagsWithHierarchy
  @InComponentCode char(2),
  @InComponentID numeric

AS
BEGIN

CREATE TABLE #TagDisplay (
       InterestCategoryID numeric,
       ParentCategoryID numeric,
       Name varchar(255),
       SortOrder numeric,
       CategoryDepth numeric,
       SelectedFlag char(1))

-- First insert selected tags ...

INSERT INTO #TagDisplay (
       InterestCategoryID,
       ParentCategoryID,
       Name,
       SortOrder,
       CategoryDepth,
       SelectedFlag)
SELECT b.InterestCategoryID,
       b.ParentCategoryID,
       b.Name,
       b.SortOrder,
       b.CategoryDepth,
       'Y'
  FROM Component_Interest_Category a WITH (NOLOCK),
       Interest_Category b WITH (NOLOCK)
WHERE a.InterestCategoryID = b.InterestCategoryID
   AND a.ComponentCode = @InComponentCode
   AND a.ComponentID = @InComponentID

-- Now add the ancestors ...

WHILE @@RowCount > 0
INSERT INTO #TagDisplay (
       InterestCategoryID,
       ParentCategoryID,
       Name,
       SortOrder,
       CategoryDepth)
SELECT DISTINCT a.InterestCategoryID,
       a.ParentCategoryID,
       a.Name,
       a.SortOrder,
       a.CategoryDepth
  FROM #TagDisplay t,
       Interest_Category a WITH (NOLOCK)
  LEFT OUTER JOIN #TagDisplay t2
    ON a.InterestCategoryID = t2.InterestCategoryID
WHERE a.InterestCategoryID = t.ParentCategoryID
   AND t2.InterestCategoryID IS NULL

SELECT *
  FROM #TagDisplay
ORDER BY SortOrder

END

GO
GRANT EXECUTE ON  [dbo].[amsp_GetTagsWithHierarchy] TO [IMIS]
GO